<?php
class keu_transaksi_model extends MY_Model {
	var $model = 'gltrans';
	
	
	function GetJSON()
	{
		$ls_sql	= "	SELECT	TRANS.ID_DOK , OFC.NAMA_KANTOR, TO_CHAR(TRANS.TGL_TRANSAKSI,'DD/MM/YYYY') TGL_TRANSAKSI, 
							TRANS.KETERANGAN, TRANS.ID_COA,  TRANS.KD_VALUTA
					FROM	KEU_TRANSAKSI TRANS, SW_KANTOR OFC
					WHERE	
							OFC.KD_KANTOR = TRANS.KD_KANTOR  AND
							NVL(STATUS_VERIFIKASI,'N')='N' AND
							TRANS.TGL_TRANSAKSI BETWEEN 
							TO_DATE('".date_format3(post('date_from'), 'YYYY-MM-DD')."','YYYY-MM-DD') AND 
							TO_DATE('".date_format3(post('date_to'), 'YYYY-MM-DD')."','YYYY-MM-DD') AND					
							(
								UPPER(OFC.NAMA_KANTOR) LIKE UPPER('%".post('keyword')."%') OR
								UPPER(TRANS.KD_VALUTA) LIKE UPPER('%".post('keyword')."%') OR
								UPPER(TRANS.KETERANGAN) LIKE UPPER('%".post('keyword')."%') OR
								UPPER(TRANS.ID_DOK) LIKE UPPER('%".post('keyword')."%')
							)
					ORDER BY ".(post('sort')=='' ? 'ID_DOK' : post('sort'))." ".(post('order')=='' ? 'ASC' : post('order'));
					
		return json_encode( $this->paging($ls_sql) );
	}
	
	
	function GetDetailJSON($as_doc='')
	{
		$ls_sql	= "	SELECT	*
					FROM	KEU_TRANSAKSI_DETAIL
					WHERE ID_DOK = '".$as_doc."'";
		$result	= $this->db->query($ls_sql);			
		return json_encode( $result->row_array() );
	}

	
	function Add($arr_data){
		$this->db->insert('KEU_TRANSAKSI', $arr_data);
	}
	
	function Update($arr_data, $arr_rule){
		$this->db->update('KEU_TRANSAKSI', $arr_data, $arr_rule);
	}
	
	function GetSingle($as_code){
		$result = $this->db->get_where('KEU_TRANSAKSI', array('ID_DOK' => $as_code));
		if($result->num_rows() == 1){
			return $result->row_array();
		}else{
			return NULL;
		}
	}
	
	function GetSingleView($as_code)
	{
		$ls_sql	= "	SELECT	d.ID_DOK,
							d.ID_REFF,
							d.KD_TIPE_JURNAL,
							d.KD_KANTOR,
							TO_CHAR(d.TGL_TRANSAKSI,'DD/MM/YYYY') TGL_TRANSAKSI,
							d.ID_COA,
							d.KURS,
							d.KD_VALUTA,
							d.STATUS_POSTING,
							d.USER_POSTING,
							TO_CHAR(d.TGL_POSTING,'DD/MM/YYYY') TGL_POSTING,
							d.STATUS_VERIFIKASI,
							d.USER_VERIFIKASI,
							TO_CHAR(d.TGL_VERIFIKASI,'DD/MM/YYYY') TGL_VERIFIKASI,
							d.CREATE_USER,
							d.CREATE_DATE,
							d.MODIFY_USER,
							d.MODIFY_DATE,
							d.KETERANGAN
					FROM	KEU_TRANSAKSI d
					WHERE	ID_DOK = '".$as_code."'";
							
		$result	= $this->db->query($ls_sql);
		
		if($result->num_rows() == 1){
			return $result->row_array();
		}else{
			return NULL;
		}
	}
	
	
	function DeleteSingle($as_code)
	{
		try {
			$this->db->delete('KEU_TRANSAKSI_DETAIL', array('ID_DOK' => $as_code));
			$this->db->delete('KEU_TRANSAKSI', array('ID_DOK' => $as_code));
			
			return '';
		} catch(Exception $e) {
			return $e->getMessage();
		}
	}
	
	
	function GetKurs($as_currency, $adt_eff_date){
		$as_currency = trim($as_currency);
		$result = $this->db->get_where('KEU_KURS', array('KD_VALUTA' => $as_currency, 'TGL_EFEKTIF' => $adt_eff_date));
		if($result->num_rows() == 1){
			return $result->row_array();
		}else{
			$ls_sql = "SELECT * FROM KEU_KURS WHERE KD_VALUTA = '".$as_currency."' AND  TGL_EFEKTIF = (SELECT MAX(TGL_EFEKTIF) FROM KEU_KURS WHERE KD_VALUTA = '".$as_currency."')";
			$result	= $this->db->query($ls_sql);
			if($result->num_rows() == 1){
				return $result->row_array();
			}else{
				$ls_sql = "SELECT 1 HARGA_BELI, 1 HARGA_JUAL, 1 HARGA_RATA FROM DUAL";
				$result	= $this->db->query($ls_sql);
				return $result->row_array();
			}
		}
	}
	
	
	function GetSqlTransaksi($as_coa, $adt_from, $adt_to, $as_office='', $as_project='', $as_activity='')
	{
		$ls_sql	= "	SELECT T.TGL_TRANSAKSI, D.* , C.NAMA_COA, 
					CASE NVL(D.DEBET,0) WHEN 0 THEN D.KREDIT_C ELSE 0 END  VIEW_D,
					CASE NVL(D.KREDIT,0) WHEN 0 THEN D.DEBET_C ELSE 0 END  VIEW_C
					FROM KEU_TRANSAKSI_DETAIL D 
								INNER JOIN KEU_TRANSAKSI T ON D.ID_DOK = T.ID_DOK 
								INNER JOIN KEU_COA C ON D.ID_COA = C.ID_COA
					WHERE D.ID_DOK IN (SELECT ID_DOK FROM KEU_TRANSAKSI_DETAIL WHERE ID_COA = '".$as_coa."' ) AND
					D.ID_COA<>'".$as_coa."' AND
					/*T.STATUS_POSTING = 'Y' AND*/
					T.KD_KANTOR LIKE '%".$as_office."%' AND 
					T.TGL_TRANSAKSI BETWEEN TO_DATE('".date_format3($adt_from, 'YYYY-MM-DD')."','YYYY-MM-DD')
									AND TO_DATE('".date_format3($adt_to, 'YYYY-MM-DD')."','YYYY-MM-DD')";
					
					
		return $ls_sql;
	}
	
	function GetGridDataTransaksi(){
		$ls_sql = $this->GetSqlTransaksi(post('id_coa'), post('date_from'), post('date_to'), post('kd_kantor'), NULL, NULL);
		$ls_sql .=" ORDER BY ".(post('sort')=='' ? 'T.TGL_TRANSAKSI' : post('sort'))." ".(post('order')=='' ? 'asc' : post('order'));
		
		return json_encode( $this->paging($ls_sql) );
	}
	
	function GetDataTransaksi($as_coa, $adt_from, $adt_to, $as_office='', $as_project='', $as_activity=''){
		$ls_sql = $this->GetSqlTransaksi($as_coa, $adt_from, $adt_to, $as_office, $as_project, $as_activity);
		$ls_sql .=" ORDER BY T.TGL_TRANSAKSI, T.ID_DOC, VIEW_D  ";
		$result = $this->db->query($ls_sql);
		if($result->num_rows() > 0){
			return $result->result_array();
		}else{
			return NULL;
		}
	}	
	
	
	function GetSqlJurnal($as_jurnaltype, $adt_from, $adt_to, $as_office='', $as_project='', $as_activity='')
	{
		$ls_sql	= "	SELECT T.TGL_TRANSAKSI, T.KETERANGAN, '' TRANS_TYPE_ID, D.* , C.NAMA_COA, 
					CASE IFNULL(D.DEBET,0) WHEN 0 THEN D.KREDIT_C ELSE 0 END  VIEW_D,
					CASE IFNULL(D.KREDIT,0) WHEN 0 THEN DEBET_C ELSE 0 END  VIEW_C
					FROM KEU_TRANSAKSI_DETAIL D 
								INNER JOIN KEU_TRANSAKSI T ON D.ID_DOC = T.ID_DOC 
								INNER JOIN KEU_COA C ON D.CODE_COA = C.CODE 
					WHERE 
					/*T.STATUS_POSTING = 'Y' AND*/
					T.KD_KANTOR LIKE '%".$as_office."%' AND 
					T.TGL_TRANSAKSI BETWEEN TO_DATE('".date_format3($adt_from, 'YYYY-MM-DD')."','YYYY-MM-DD')
									AND TO_DATE('".date_format3($adt_to, 'YYYY-MM-DD')."','YYYY-MM-DD')";
		
		return $ls_sql;
	}
	
	function GetGridDataJurnal(){
		$ls_sql = $this->GetSqlJurnal('', post('date_from'), post('date_to'), post('KD_KANTOR'), NULL, NULL);
		$ls_sql .=" ORDER BY ".(post('sort')=='' ? 'T.TGL_TRANSAKSI' : post('sort'))." ".(post('order')=='' ? 'ASC' : post('order'));
		return json_encode( $this->paging($ls_sql) );
	}
	
	function GetDataJurnal($as_jurnal, $adt_from, $adt_to, $as_office='', $as_project='', $as_activity){
		$ls_sql = $this->GetSqlJurnal('', $adt_from, $adt_to, $as_office, $as_project, $as_activity);
		$ls_sql .=" ORDER BY T.TGL_TRANSAKSI, T.ID_DOC, VIEW_D";
		echo $ls_sql;
		$result = $this->db->query($ls_sql);
		if($result->num_rows() > 0){
			return $result->result_array();
		}else{
			return NULL;
		}
	}	
	
	function GetBalance($as_coa, $adt_from, $adt_to, $as_office='', $as_project='', $as_activity=''){
		$adt_from = date_format3($adt_from, 'YYYY-MM-DD');
		$adt_to = date_format3($adt_to, 'YYYY-MM-DD');
		
		$ls_sql = "SELECT
						gl_coa.code code_coa,
						gl_coa.coa_name,
						CASE gl_coa.st_pos
						WHEN 'D' THEN
							sum(gl.db) - sum(gl.cr)
						ELSE
							sum(gl.cr) - sum(gl.db)
						END saldo
					FROM
						(
							SELECT
								YEAR,
								code_office,
								code_coa,
								code_activity,
								project_code,
								sum(
									CASE MONTH('".$adt_from."')-1
									WHEN 1 THEN
										NVL(begin_db, 0)+ NVL(db1, 0)
									WHEN 2 THEN
										NVL(begin_db, 0)+ NVL(db1, 0)+ NVL(db2, 0)
									WHEN 3 THEN
										NVL(begin_db, 0)+ NVL(db1, 0)+ NVL(db2, 0)+ NVL(db3, 0)
									WHEN 4 THEN
										NVL(begin_db, 0)+ NVL(db1, 0)+ NVL(db2, 0)+ NVL(db3, 0)+ NVL(db4, 0)
									WHEN 5 THEN
										NVL(begin_db, 0)+ NVL(db1, 0)+ NVL(db2, 0)+ NVL(db3, 0)+ NVL(db4, 0)+ NVL(db5, 0)
									WHEN 6 THEN
										NVL(begin_db, 0)+ NVL(db1, 0)+ NVL(db2, 0)+ NVL(db3, 0)+ NVL(db4, 0)+ NVL(db5, 0)+ NVL(db6, 0)
									WHEN 7 THEN
										NVL(begin_db, 0)+ NVL(db1, 0)+ NVL(db2, 0)+ NVL(db3, 0)+ NVL(db4, 0)+ NVL(db5, 0)+ NVL(db6, 0)+ NVL(db7, 0)
									WHEN 8 THEN
										NVL(begin_db, 0)+ NVL(db1, 0)+ NVL(db2, 0)+ NVL(db3, 0)+ NVL(db4, 0)+ NVL(db5, 0)+ NVL(db6, 0)+ NVL(db7, 0)+ NVL(db8, 0)
									WHEN 9 THEN
										NVL(begin_db, 0)+ NVL(db1, 0)+ NVL(db2, 0)+ NVL(db3, 0)+ NVL(db4, 0)+ NVL(db5, 0)+ NVL(db6, 0)+ NVL(db7, 0)+ NVL(db8, 0)+ NVL(db9, 0)
									WHEN 10 THEN
										NVL(begin_db, 0)+ NVL(db1, 0)+ NVL(db2, 0)+ NVL(db3, 0)+ NVL(db4, 0)+ NVL(db5, 0)+ NVL(db6, 0)+ NVL(db7, 0)+ NVL(db8, 0)+ NVL(db9, 0)+ NVL(db10, 0)
									WHEN 11 THEN
										NVL(begin_db, 0)+ NVL(db1, 0)+ NVL(db2, 0)+ NVL(db3, 0)+ NVL(db4, 0)+ NVL(db5, 0)+ NVL(db6, 0)+ NVL(db7, 0)+ NVL(db8, 0)+ NVL(db9, 0)+ NVL(db10, 0)+ NVL(db11, 0)
									WHEN 12 OR 0 THEN
										NVL(begin_db, 0)+ NVL(db1, 0)+ NVL(db2, 0)+ NVL(db3, 0)+ NVL(db4, 0)+ NVL(db5, 0)+ NVL(db6, 0)+ NVL(db7, 0)+ NVL(db8, 0)+ NVL(db9, 0)+ NVL(db10, 0)+ NVL(db11, 0)+ NVL(db12, 0)
									END
								)db,
								sum(
									CASE MONTH('".$adt_from."')-1
									WHEN 1 THEN
										NVL(begin_cr, 0)+ NVL(cr1, 0)
									WHEN 2 THEN
										NVL(begin_cr, 0)+ NVL(cr1, 0)+ NVL(cr2, 0)
									WHEN 3 THEN
										NVL(begin_cr, 0)+ NVL(cr1, 0)+ NVL(cr2, 0)+ NVL(cr3, 0)
									WHEN 4 THEN
										NVL(begin_cr, 0)+ NVL(cr1, 0)+ NVL(cr2, 0)+ NVL(cr3, 0)+ NVL(cr4, 0)
									WHEN 5 THEN
										NVL(begin_cr, 0)+ NVL(cr1, 0)+ NVL(cr2, 0)+ NVL(cr3, 0)+ NVL(cr4, 0)+ NVL(cr5, 0)
									WHEN 6 THEN
										NVL(begin_cr, 0)+ NVL(cr1, 0)+ NVL(cr2, 0)+ NVL(cr3, 0)+ NVL(cr4, 0)+ NVL(cr5, 0)+ NVL(cr6, 0)
									WHEN 7 THEN
										NVL(begin_cr, 0)+ NVL(cr1, 0)+ NVL(cr2, 0)+ NVL(cr3, 0)+ NVL(cr4, 0)+ NVL(cr5, 0)+ NVL(cr6, 0)+ NVL(cr7, 0)
									WHEN 8 THEN
										NVL(begin_cr, 0)+ NVL(cr1, 0)+ NVL(cr2, 0)+ NVL(cr3, 0)+ NVL(cr4, 0)+ NVL(cr5, 0)+ NVL(cr6, 0)+ NVL(cr7, 0)+ NVL(cr8, 0)
									WHEN 9 THEN
										NVL(begin_cr, 0)+ NVL(cr1, 0)+ NVL(cr2, 0)+ NVL(cr3, 0)+ NVL(cr4, 0)+ NVL(cr5, 0)+ NVL(cr6, 0)+ NVL(cr7, 0)+ NVL(cr8, 0)+ NVL(cr9, 0)
									WHEN 10 THEN
										NVL(begin_cr, 0)+ NVL(cr1, 0)+ NVL(cr2, 0)+ NVL(cr3, 0)+ NVL(cr4, 0)+ NVL(cr5, 0)+ NVL(cr6, 0)+ NVL(cr7, 0)+ NVL(cr8, 0)+ NVL(cr9, 0)+ NVL(cr10, 0)
									WHEN 11 THEN
										NVL(begin_cr, 0)+ NVL(cr1, 0)+ NVL(cr2, 0)+ NVL(cr3, 0)+ NVL(cr4, 0)+ NVL(cr5, 0)+ NVL(cr6, 0)+ NVL(cr7, 0)+ NVL(cr8, 0)+ NVL(cr9, 0)+ NVL(cr10, 0)+ NVL(cr11, 0)
									WHEN 12 OR 0 THEN
										NVL(begin_cr, 0)+ NVL(cr1, 0)+ NVL(cr2, 0)+ NVL(cr3, 0)+ NVL(cr4, 0)+ NVL(cr5, 0)+ NVL(cr6, 0)+ NVL(cr7, 0)+ NVL(cr8, 0)+ NVL(cr9, 0)+ NVL(cr10, 0)+ NVL(cr11, 0)+ NVL(cr12, 0)
									END
								)cr
							FROM
								gl_ledger
							WHERE
								YEAR = case when MONTH('".$adt_from."')-1=0 then YEAR('".$adt_from."')-1 else YEAR('".$adt_from."') end
							GROUP BY
								YEAR,
								code_office,
								code_coa,
								code_activity,
								project_code
						)gl
					RIGHT JOIN gl_coa ON gl.code_coa = gl_coa.`code`
					WHERE
						gl_coa. CODE = '".$as_coa."'
					AND ifnull(gl.code_activity, '') LIKE '%".$as_activity."%'
					AND ifnull(gl.project_code, '') LIKE '%".$as_project."%'
					AND ifnull(gl.code_office, '') LIKE '%".$as_office."%'
					GROUP BY
						gl_coa.code,
						gl_coa.coa_name";
		
	
		$result = $this->db->query($ls_sql);
		if($result->num_rows() > 0){
			return $result->result_array();
		}else{
			return NULL;
		}
	
	}
	
	
	
	function GetSqlBukuKasUmum($adt_from, $adt_to, $as_office='', $as_project='', $as_activity='')
	{
		$ls_sql	= "	SELECT
						gl_trans.trans_date,
						det.description trans_desc,
						gl_trans.ID_DOC,
						CONCAT(
							act.old_code,
							ofc.old_code,
							'.',
							RIGHT(det.project_code, 3),
							' ',
							LEFT(det.code_coa, 3),
							'.',
							RIGHT(det.code_coa, 3)
						) mata_anggaran,
						det.kredit_c kredit,
						det.debet_c debet
					FROM
						gl_trans
					INNER JOIN gl_trans_detail det ON det.ID_DOC = gl_trans.ID_DOC
					INNER JOIN gl_coa ON det.code_coa = gl_coa.CODE
					INNER JOIN sw_activity act ON det.code_activity = act.`code`
					INNER JOIN pm_project prj ON det.project_code = prj.project_code
					INNER JOIN SW_KANTOR ofc ON gl_trans.code_office = ofc.CODE
					WHERE 
						/*gl_trans.st_posting = 'Y' AND */
						gl_trans.code_office like '%".$as_office."%' AND 
						det.project_code like '%".$as_project."%' AND 
						det.code_activity like '%".$as_activity."%' AND
						gl_trans.trans_date between '".date_format3($adt_from, 'YYYY-MM-DD')."' and '".date_format3($adt_to, 'YYYY-MM-DD')."'
						
					";
					
					
		return $ls_sql;
	}
	
	function GetGridDataBukuKasUmum(){
		$ls_sql = $this->GetSqlBukuKasUmum(post('date_from'), post('date_to'), post('KD_KANTOR'), post('project_code'), post('code_activity'));
		$ls_sql .=" ORDER BY ".(post('sort')=='' ? 'gl_trans.trans_date,  gl_trans.ID_DOC' : post('sort'))." ".(post('order')=='' ? 'asc' : post('order'));
		
		return json_encode( $this->paging($ls_sql) );
	}
	
	function GetDataBukuKasUmum($adt_from, $adt_to, $as_office='', $as_project='', $as_activity=''){
		$ls_sql = $this->GetSqlBukuKasUmum($adt_from, $adt_to, $as_office, $as_project, $as_activity);
		$ls_sql .=" ORDER BY gl_trans.trans_date, gl_trans.ID_DOC";
		
		$result = $this->db->query($ls_sql);
		if($result->num_rows() > 0){
			return $result->result_array();
		}else{
			return NULL;
		}
	}	
	
	function GetCoa($as_coa='', $as_coa2=''){
		if ($as_coa2=='') $as_coa2=$as_coa;
		$ls_sql = "select code from gl_coa where code between '".$as_coa."' and '".$as_coa2."'";
		$result = $this->db->query($ls_sql);
		return $result->result_array();
	}
	
}
?>